﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_InitMsdb]
#-- Purpose:		Creates the RestoreUser role/permissions; adds indexes
#--	Last Update:	04/17/2013
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_InitMsdb]
AS

SET NOCOUNT ON

/* Add some indexes to help the queries on [msdb].[dbo].[backupset] to run a little better */
DECLARE @sCommand nvarchar(MAX)
SELECT	@sCommand = 'USE msdb;
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[backupset]'') AND name = N''custom_backupset_checkpoint_type'')
	CREATE NONCLUSTERED INDEX custom_backupset_checkpoint_type ON [dbo].[backupset] 
	(
		[checkpoint_lsn],
		[type]
	)
	INCLUDE 
	(
		[backup_set_id]
	);'
EXEC	(@sCommand)

SELECT	@sCommand = 'USE msdb;
IF NOT EXISTS (SELECT * FROM [msdb].sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[backupset]'') AND name = N''custom_backupset_dbname_startdate'')
	CREATE NONCLUSTERED INDEX custom_backupset_dbname_startdate ON [dbo].[backupset] 
	(
		[database_name] ASC,
		[backup_start_date] ASC
	)
	INCLUDE 
	(
		[first_recovery_fork_guid], 
		[last_recovery_fork_guid]
	);'
EXEC	(@sCommand)

SELECT	@sCommand = 'USE msdb;
IF NOT EXISTS (SELECT * FROM [msdb].sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[backupset]'') AND name = N''custom_backupset_recovery_query_speed'')
	CREATE NONCLUSTERED INDEX custom_backupset_recovery_query_speed ON [dbo].[backupset] 
	(
		[last_lsn],
		[type],
		[last_recovery_fork_guid],
		[database_guid],
		[backup_set_id]
	);'
EXEC	(@sCommand)

SELECT	@sCommand = 'USE msdb;
IF NOT EXISTS (SELECT * FROM [msdb].sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[backupset]'') AND name = N''custom_backupset_type'')
	CREATE NONCLUSTERED INDEX custom_backupset_type ON [dbo].[backupset] 
	(
		[type]
	)
	INCLUDE 
	(
		[backup_set_id],
		[checkpoint_lsn]
	);'
EXEC	(@sCommand)

SELECT	@sCommand = 'USE msdb;
IF NOT EXISTS (SELECT * FROM [msdb].sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[backupset]'') AND name = N''custom_backupset_name_INC_bsid_msid_sdate'')
	CREATE NONCLUSTERED INDEX custom_backupset_name_INC_bsid_msid_sdate ON [dbo].[backupset] 
	(
		[database_name]
	)
	INCLUDE 
	(
		[backup_set_id],
		[media_set_id],
		[backup_start_date]
	);'
EXEC	(@sCommand)

SELECT	@sCommand = 'USE msdb;
IF NOT EXISTS (SELECT * FROM [msdb].sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[backupset]'') AND name = N''custom_backup_list'')
	CREATE NONCLUSTERED INDEX custom_backup_list
		ON [dbo].[backupset] 
		(
			[database_name],
			[backup_start_date],
			[name]
		)
		INCLUDE 
		(
			[backup_set_id],
			[media_set_id],
			[type],
			[backup_size],
			[recovery_model],
			[has_bulk_logged_data]
		);'
EXEC	(@sCommand)

SELECT	@sCommand = 'USE msdb;
IF NOT EXISTS (SELECT * FROM [msdb].sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[backupset]'') AND name = N''custom_backupset_recwindow'')
	CREATE NONCLUSTERED INDEX custom_backupset_recwindow ON [dbo].[backupset]
	(
		database_name,
		is_copy_only,
		backup_set_id,
		checkpoint_lsn,
		database_backup_lsn
	);'
EXEC	(@sCommand)

---------------------------------
--- Setup database roles
---------------------------------
SELECT	@sCommand = 'USE msdb;
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''RestoreUser'' AND type = ''R'')
	CREATE ROLE [RestoreUser] AUTHORIZATION dbo;
GRANT SELECT ON [dbo].[backupfile] TO RestoreUser;
GRANT SELECT ON [dbo].[backupfile] TO RestoreUser;
GRANT SELECT ON [dbo].[backupmediafamily] TO RestoreUser;
GRANT SELECT ON [dbo].[backupmediaset] TO RestoreUser;
GRANT SELECT ON [dbo].[backupset] TO RestoreUser;'
EXEC	(@sCommand)

SET NOCOUNT OFF